﻿--获取单个人的岗位异动记录
CREATE proc [dbo].[proc_Employee_PostMove_GetPersonList]
	@eid int
as
	select
		Id, 
		Eid,
		(select EName from Employee E where EId=Id) as EName,
		(select CompanyName From Managementarea Where Id=((select Mid from Company where id=ACompanyId))) as  AManagementarea,
		(select CompanyName From Managementarea Where Id=((select Mid from Company where id=BCompanyId))) as  BManagementarea,
		(select CompanyName from Company where id=ACompanyId) as ACompanyName,
		(select CompanyName from Company where id=BCompanyId) as BCompanyName,
		(select DName from Department where id= (SELECT e.DepartmentId from employee E where Employee_PostMove.EId=E.Id)) as ADName,
		(select DName from Department where id= BDepartmentId) as BDName,
		(select Title from Grade G where id= AGradeId) as AGradeName,
		(select Title from Grade G where id= BGradeId) as BGradeName,
		(select PostName from Post where id= APostId) as APostName,
		(select PostName from Post where id= BPostId) as BPostName,
		(select title from post_category where id=(select postcategory from Post where id= APostId)) as APostCategory,
		(select title from post_category where id=(select postcategory from Post where id= BPostId)) as BPostCategory,
		(select Title from Employee_State S where id=AState) as AState,
		BZhiWu,
		AZhiWu,
		Reason,
		(select ename from employee where username=Proposer) as Proposer,
		ApplionTime,
		FirstAudit,
		FirstMemo,
		(select ename from employee where username=FirstAuditPerson) as FirstAuditPerson,
		FirstAuditTime,
		SecondAudit,
		SecondMemo,
		(select ename from employee where username=SecondAuditPerson) as SecondAuditPerson,
		SecondAuditTime,
		ISNULL(Execdate,'1900-01-01') AS Execdate,
		ISNULL(BXZJB,'') AS BXZJB,
		ISNULL(AXZJB,'') AS AXZJB
	from
		Employee_PostMove
	where 
		eid=@eid
		and secondAudit=1